﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVTVKPAYData : BaseFunction
    {
        
        public MVTVKPAYData()
            : base()
        {

        }
        public MVTVKPAYData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPAY(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKPAY";
            string PI = dwABPOS["PI"].ToString().Trim();
            string PO = dwABPOS["PO"].ToString().Trim();
            string KUNDNR = dwABPOS["KUNDNR"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select STATUS,CO,OPENITEM,ENTRYDATE,CSCODE,CUSTOMER,PAYMENT,DES,CURRENCY,ORDERVALUE, "
                    + " PDEPOSIT,ADEPOSIT,GOODSVALUE,PBEFORSHIP,ABEFORSHIP,PAFTERSHIP,VRTOTVALUES,AVRVALUES, "
                    + " DECODE(STATUS,'J',0,decode(GOODSVALUE,0,ORDERVALUE-ADEPOSIT,(CASE WHEN GOODSVALUE>ORDERVALUE "
                    + " then ROUND(GOODSVALUE-ADEPOSIT-ABEFORSHIP-AVRVALUES+ "
                    + "   (ABEFORSHIP/GOODSVALUE*AVRVALUES)+(ADEPOSIT/GOODSVALUE*AVRVALUES)) "
                    + "   ELSE ROUND(ORDERVALUE-ADEPOSIT-ABEFORSHIP-AVRVALUES+ "
                    + "   (ABEFORSHIP/GOODSVALUE*AVRVALUES)+(ADEPOSIT/ORDERVALUE*AVRVALUES))  "
                    + "   END ))) AS REMAINVALUE,AFTERDAYS,PLVDAT "
                    + " from (select MIN(C.VKPERL) AS STATUS,A.VKKAUFKRS||'-'||TO_NUMBER(A.VKKAUFLFD) AS CO, "
                    + " A.VKKNEUDAT AS ENTRYDATE,TO_NUMBER(A.VKKKUNDNR) AS CSCODE,TRIM(D.KDKUNDNA) AS CUSTOMER, "
                    + " round(A.VKKGESWERT/(A.VKKKURS*0.0001))AS ORDERVALUE,A.VKKWAEHRSL AS CURRENCY, "
                    + " A.VKKZAHLBED AS PAYMENT,A.VKKOPITEM AS OPENITEM,trim(B.ZABBEZ) AS DES, "
                    + " ROUND(A.VKKGESWERT/(A.VKKKURS*0.0001)*B.zabskonp1*0.0001) AS PDEPOSIT, "
                    + " NVL((SELECT GLOAMICUC*0.01 FROM GLOPITEM G WHERE G.GLOMANDANT = A.VKKMANDANT  "
                    + " AND G.GLOACCOUNT = TO_NUMBER(A.VKKKUNDNR) AND G.GLOITEM = A.VKKOPITEM),0) AS ADEPOSIT, "
                    + " ROUND(SUM((case when C.VKPZUGET>0 then  "
                    + " (C.VKPPOSWERT/(C.VKPKURS*0.0001))-(C.VKPMENGE1*0.01*c.vkppreis*0.001)else 0 end)+ "
                    + " (C.VKPZUGET*0.01*c.vkppreis*0.001))) AS GOODSVALUE, "
                    + " ROUND(SUM((case when C.VKPZUGET>0 then  "
                    + " (C.VKPPOSWERT/(C.VKPKURS*0.0001))-(C.VKPMENGE1*0.01*c.vkppreis*0.001)else 0 end)+ "
                    + " (C.VKPZUGET*0.01*c.vkppreis*0.001))*B.zabskonp2*0.0001) AS PBEFORSHIP, "
                    + " nvl((SELECT SUM(VRKWAWERT*0.01) FROM VRKOPF V WHERE V.VRKMANDANT = A.VKKMANDANT "
                    + " AND TRIM(V.VRKADINVNO) = TRIM(A.VKKOPITEM) AND V.VRKFIBUKZ <> 'S' "
                    + " AND V.VRKREART = 'BE' ),0) AS ABEFORSHIP, "
                    + " ROUND(SUM((case when C.VKPZUGET>0 then  "
                    + " (C.VKPPOSWERT/(C.VKPKURS*0.0001))-(C.VKPMENGE1*0.01*c.vkppreis*0.001)else 0 end)+ "
                    + " (C.VKPZUGET*0.01*c.vkppreis*0.001))*B.zabvorzz*0.0001) AS PAFTERSHIP, "
                    + " nvl((SELECT SUM(A1.VRPGESWERT*0.001) FROM VRPOS A1,VRKOPF B1 "
                    + " WHERE A1.VRPMANDANT = B1.VRKMANDANT AND A1.VRPRENR = B1.VRKRENR "
                    + " AND B1.VRKFIBUKZ <> 'S' AND B1.VRKREART = 'RE'  "
                    + " AND A1.VRPMANDANT = A.VKKMANDANT AND A1.VRPAUFKRS = A.VKKAUFKRS "
                    + " AND A1.VRPAUFLFD = A.VKKAUFLFD ),0) AS VRTOTVALUES, "
                    + " nvl((SELECT SUM(A1.VRPGESWERT*0.001) FROM VRPOS A1,VRKOPF B1 "
                    + " WHERE A1.VRPMANDANT = B1.VRKMANDANT AND A1.VRPRENR = B1.VRKRENR "
                    + " AND B1.VRKFIBUKZ <> 'S' AND B1.VRKREART = 'RE' AND B1.VRKKOMPL = 'J' "
                    + " AND A1.VRPMANDANT = A.VKKMANDANT AND A1.VRPAUFKRS = A.VKKAUFKRS "
                    + " AND A1.VRPAUFLFD = A.VKKAUFLFD),0) AS AVRVALUES, "
                    + " B.zabntage AS AFTERDAYS,max(c.vkplvdat) as PLVDAT "
                    + " from vkkopf a,ZABED B ,VKPOS C,KDSTAMM D "
                    + " where A.VKKMANDANT = C.VKPMANDANT AND A.VKKSA = C.VKPSA "
                    + " AND A.VKKAUFKRS = C.VKPAUFKRS AND A.VKKAUFLFD = C.VKPAUFLFD "
                    + " AND A.VKKMANDANT = B.ZABMANDANT AND A.VKKZAHLBED = B.ZABNR "
                    + " AND A.VKKMANDANT = D.KDMANDANT AND A.VKKKUNDNR = D.KDKUNDNR "
                    + " AND a.vkksa<>'AJ' AND B.ZABSPRACHKZ = 'V ' "
                    + " AND (TRIM(A.VKKAUFKRS)||'-'||TO_NUMBER(A.VKKAUFLFD)||'-'||TRIM(D.KDKUNDNA) = '" + PI + "' OR '" + PI + "' = 'AAA')  "
                    + " AND (TRIM(A.VKKBESTNR) = '" + PO + "' OR '" + PO + "' = 'AAA') "
                    + " AND (trim(D.KDKUNDNA) = '" + KUNDNR + "' OR '" + KUNDNR + "' = 'AAA' ) "
                    + " AND (A.VKKNEUDAT >= " + FROMDATE + " AND  A.VKKNEUDAT <= " + ENDDATE + ") "
                    + " GROUP BY A.VKKMANDANT ,A.VKKAUFKRS,A.VKKAUFLFD,A.VKKNEUDAT,A.VKKKUNDNR,D.KDKUNDNA, "
                    + " A.VKKGESWERT,A.VKKKURS,A.VKKWAEHRSL,A.VKKZAHLBED,A.VKKOPITEM,B.ZABBEZ, "
                    + " B.zabskonp1,B.zabskonp2,B.zabvorzz,B.zabntage ) "                    
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CUSTOMER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CUSTOMER"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildMVTVKPAYData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPAY(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}